/* ******************************************************************************************************* */
/* ********************************** Retrieves mutual fund voting data ************************************/
/* ******************************************************************************************************* */

**** Merge NPX voting data with vote results ****;
proc sql;
  create table votes1 as
  select a.*
  from iss.Voteanalysis (keep=institutionName institutionID FundId FundName CompanyID CompanyName SecurityID version meetingID MeetingDate ISSAgendaItemID itemonagendaid agendageneraldesc 
    seqNumber BallotItemNumber ItemDesc MgtRec FundVote) as a, iss.Vote_Results2003_2018 as b
  where a.itemOnAgendaId = b.itemOnAgendaId and b.voterequirement not in (0.01, .) and b.voteResult not in ('', 'Pending', 'Withdrawn', 'Not Disclosed', 'Not Applicable') and base ne ''; 
quit;

proc sort data = drop.Proposal_list out = all_agenda nodupkey; by ISSAgendaItemID;

proc sql;
  create table votes1 as
  select a.*, b.issue
  from votes1 as a left join all_agenda as b
  on a.ISSAgendaItemID = b.ISSAgendaItemID; 
quit;

proc sort data = iss.Vote_Results2003_2018 (keep=companyid meetingID cusip ticker recordDate) out = identifier nodupkey; by companyid meetingID ticker cusip;

proc sort data = identifier (keep=companyid ticker cusip) out = identifier2 nodupkey; by companyid; 

proc sort data = votes1 (keep=companyid meetingID CompanyName SecurityID MeetingDate) out = votes_identifier nodupkey; by companyid meetingID; 

proc sql;
  create table votes_identifier2 as
  select a.*, b.ticker, b.cusip, recordDate
  from votes_identifier as a left join identifier as b
  on a.companyid = b.companyid and a.meetingID = b.meetingID;
quit;

data votes_identifier2a; set votes_identifier2;
  if ticker ne ''; 

data votes_identifier2b; set votes_identifier2;
  if ticker = ''; 
  drop ticker cusip recordDate;

proc sql;
  create table votes_identifier2b as 
  select a.*, b.ticker, b.cusip
  from votes_identifier2b as a, identifier2 as b
  where a.companyid = b.companyid;
quit;

data votes_identifier3; set votes_identifier2a votes_identifier2b;
run;

proc sql;
  create table votes1 as
  select a.*, b.ticker, b.cusip, recordDate
  from votes1 as a, votes_identifier3 as b
  where a.companyid = b.companyid and a.meetingID = b.meetingID;
quit;

proc sort nodupkey; by _ALL_;

data temp.iss_votes; set votes1;
  drop institutionname fundname companyName version;
run;
